package com.demo.dao;

import com.demo.entity.Manager;
import com.demo.entity.Student;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface StudentMapper {
    @Delete({
        "delete from student",
        "where sid = #{sid,jdbcType=VARCHAR}"
    })
    int deleteByPrimaryKey(String sid);

    @Insert({
        "insert into student (sid, sticket, ",
        "sname, spwd, ssex, ",
        "sbirthday, smajor_id, ",
        "stel, scomment, ",
        "steacherid)",
        "values (#{sid,jdbcType=VARCHAR}, #{sticket,jdbcType=VARCHAR}, ",
        "#{sname,jdbcType=VARCHAR}, #{spwd,jdbcType=VARCHAR}, #{ssex,jdbcType=VARCHAR}, ",
        "#{sbirthday,jdbcType=VARCHAR}, #{smajorId,jdbcType=VARCHAR}, ",
        "#{stel,jdbcType=VARCHAR}, #{scomment,jdbcType=VARCHAR}, ",
        "#{steacherid,jdbcType=VARCHAR})"
    })
    int insert(Student record);

    int insertSelective(Student record);

    @Select({
        "select",
        "sid, sticket, sname, spwd, ssex, sbirthday, smajor_id, stel, scomment, steacherid",
        "from student",
        "where sid = #{sid,jdbcType=VARCHAR}"
    })
    @ResultMap("BaseResultMap")
    Student selectByPrimaryKey(String sid);

    int updateByPrimaryKeySelective(Student record);

    @Update({
        "update student",
        "set sticket = #{sticket,jdbcType=VARCHAR},",
          "sname = #{sname,jdbcType=VARCHAR},",
          "spwd = #{spwd,jdbcType=VARCHAR},",
          "ssex = #{ssex,jdbcType=VARCHAR},",
          "sbirthday = #{sbirthday,jdbcType=VARCHAR},",
          "smajor_id = #{smajorId,jdbcType=VARCHAR},",
          "stel = #{stel,jdbcType=VARCHAR},",
          "scomment = #{scomment,jdbcType=VARCHAR},",
          "steacherid = #{steacherid,jdbcType=VARCHAR}",
        "where sid = #{sid,jdbcType=VARCHAR}"
    })
    int updateByPrimaryKey(Student record);




    @Select({
            "select",
            "*",
            "from student",
            "where sid = #{0} and spwd=#{1}"
    })
    @ResultMap("BaseResultMap")
    Student logincheck(String mname, String pwd);

    @Select({
            "select",
            "*",
            "from student inner join major on student.smajor_id=major.cmajor_id"
    })
    @ResultMap("BaseResultMap")
    List<Student> selectAllWithMajor();

    @Select({
            "select",
            "*",
            "from student inner join major on student.smajor_id=major.cmajor_id",
            "where sid=#{0}"
    })
    @ResultMap("BaseResultMap")
    Student selectStuWithMajor(String id);

    @Select({
            "select",
            "*",
            "from student left  join stu_desire on student.sid=stu_desire.did",
            "left  join major on student.smajor_id=major.cmajor_id",
            "left  join teacher on (stu_desire.first_desire=teacher.tid or stu_desire.second_desire=teacher.tid",
            " or stu_desire.third_desire=teacher.tid)",
            "where major.college=#{0}"
    })
    @ResultMap("BaseResultMap")
    List<Student> selectAllWithDesire(String college);

    @Select({
            "select",
            "*",
            "from student",
            "left  join major on student.smajor_id=major.cmajor_id",
            "left  join teacher on student.steacherid=teacher.tid ",
            "where major.college=#{0}"
    })
    @ResultMap("BaseResultMap")
    List<Student> selectfinishWithDesire(String college);

    @Select({
            "select",
            "*",
            "from student",
            "left  join major on student.smajor_id=major.cmajor_id",
            "left  join teacher on student.steacherid=teacher.tid "
    })
    @ResultMap("BaseResultMap")
    List<Student> selectAllfinishWithDesire();
}